import _thread
import datetime
import os
import math
import sqlite3
import pymysql
import pandas as pd
import json
from memory_profiler import profile
from logger import logInfo, logWarning
from db_con import credibleIndex, dataIndex

# https://blog.csdn.net/wld914674505/article/details/81431128?utm_medium=distribute.pc_relevant_t0.none-task-blog-OPENSEARCH-1.channel_param&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-OPENSEARCH-1.channel_param
# 导出pip安装的所有放入一个文件中，并把通过这个安装所有的包
# 导出pip安装的所有的包：
# pip freeze > install.txt
# 在新的环境中安装导出的包
# pip install -r install.txt -i https://pypi.tuna.tsinghua.edu.cn/simple
configMysql = {'host': 'localhost', 'user': 'tradesource', 'pass': 'NtTJRmi6MRwYLAh7', 'port': '3306', 'database': 'tradesource', 'table': 'tm_trademarks'}
chunkSize = 100000
dictData = {}
# db_name = 'data_tem' + str(datetime.datetime.now().strftime('%Y-%m-%d%H%M%S')) + '.db'
db_name = 'data_tem2020-09-13143037.db'
# 建立数据库表
sq3_con = sqlite3.connect(db_name)
sq3_db = sq3_con.cursor()
sql_create = 'Create Table IF NOT EXISTS data('
sql_tem_list = []
for index in dataIndex:
    sql_tem_list.append(dataIndex[index][0] + ' ' + dataIndex[index][1])
sql_create += ','.join(sql_tem_list)
sql_create += ')'
create_table_res = sq3_db.execute(sql_create)
sq3_con.commit()


# @profile(precision=4)
def import_data_first(file_first):
    print('开始处理:' + file_first + '文件')
    logInfo('开始处理：' + file_first + ' 文件')
    chunkNum = 0
    try:
        df = pd.read_csv(file_first, chunksize=chunkSize, encoding='utf-8', header=0,
                         error_bad_lines=False, na_filter=False, na_values='NULL', low_memory=False)
    except KeyError as e:
        print('错误：' + file_first + '文件保存编码不为utf-8,现版本仅支持utf-8:' + str(e))
        logInfo('错误：' + file_first + '文件保存编码不为utf-8,现版本仅支持utf-8:' + str(e))
        exit()
    insert_sql_head = ''
    sq3_tem_con = sqlite3.connect(db_name)
    sq3_tem_db = sq3_tem_con.cursor()
    for table in df:
        chunkNum += chunkSize
        if insert_sql_head == '':
            print('建立表头')
            # 表头
            columnsList = table.columns.tolist()
            columnsIndexList = []
            keyNum = 0
            # 获取当前csv文件中是否有 注册号 分类号 并获取位置
            for columnsIndex in columnsList:
                if dataIndex[columnsIndex][0] == 'brandCode':
                    columnsIndexBrandCodeKey = keyNum
                if dataIndex[columnsIndex][0] == 'classNum':
                    columnsIndexClassNumKey = keyNum
                columnsIndexList.append(dataIndex[columnsIndex][0])
                keyNum += 1
            if not 'columnsIndexBrandCodeKey' in dir():
                print('错误：' + file_first + '文件中不存在注册号，终止导入')
                logInfo(file_first + '文件中不存在注册号，终止导入')
                exit()
            if not 'columnsIndexClassNumKey' in dir():
                print('错误：' + file_first + '文件中不存在分类号，终止导入')
                logInfo(file_first + '文件中不存在分类号，终止导入')
                exit()
            columnsIndexStr = ','.join(columnsIndexList)
            del columnsIndexList
            insert_sql_head = 'Insert into data (' + columnsIndexStr + ') values '
            del columnsIndexStr
        # 数据
        print('读取数据')
        dataList = table.values.tolist()
        values_list = []
        for data in dataList:
            values_str = ','.join('%s' % '"' + transfer_content(str(da)) + '"' for da in data)
            values_list.append('(' + values_str + ')')
        insert_sql = insert_sql_head + ','.join(values_list)
        print('开始导入' + file_first + str(chunkNum) + '条 数据到sql3中')
        try:
            sq3_tem_db.execute(insert_sql)
            sq3_tem_con.commit()
            print('批量添加' + file_first + str(chunkNum) + ' 条数据到sql3中，完成')
            logInfo('批量添加' + file_first + str(chunkNum) + ' 条数据到sql3中，完成')
        except KeyError as e:
            print('批量添加' + file_first + str(chunkNum) + ' 条数据到sqlite3中 失败，' + str(e))
            logInfo('批量添加' + file_first + str(chunkNum) + ' 条数据到sqlite3中 失败，' + str(e))

        del insert_sql
        del values_list
        del dataList

    del df
    logInfo(file_first + '数据导入sqlite3中完成')


# 将英文" 替换为“，'替换为\\\',\\替换为\\\ 入库
def transfer_content(content):
    if content is None:
        return None
    else:
        content = content.replace('"', '“')
        content = content.replace("'", "\\\'")
        content = content.replace("\\", "\\\\")
        return content


# @profile(precision=4)
def import_data():

    file_first = '注册商标基本信息.csv'
    logInfo('开始读取文件。')
    path = os.path.abspath('.')
    fileArr = []
    # 获取当前目录下所有csv文件并存入 fileArr
    for root, dirs, files in os.walk(path, topdown=False):
        for name in files:
            # 只要csv文件
            if name != '':
                if os.path.splitext(name)[1] == '.csv':
                    fileArr.append(name)
    # 判断 注册商标基本信息.csv 存不存在
    if file_first in fileArr:
        import_data_first(file_first)
    else:
        print('注册商标基本信息.csv 文件,不存在。终止导入')
        logInfo('注册商标基本信息.csv 文件,不存在。终止导入')
        exit()
    exit()
    importFileArr = ['注册商标基本信息.csv', '商标代理人信息.csv', '注册商标共有人信息.csv', '国际注册基本信息.csv', '注册商标优先权信息.csv', '注册商标商品服务信息.csv', '商标注册人信息.csv']
    for file in fileArr:
        # 跳过'注册商标基本信息.csv'
        if file == file_first:
            continue
        print('开始处理：' + file + '文件')
        logInfo('开始处理：' + file + '文件')
        if file not in importFileArr:
            continue
        try:
            df = pd.read_csv(file, chunksize=chunkSize, encoding='utf-8', header=0,
                             error_bad_lines=False, na_filter=False, na_values='NULL', low_memory=False)
        except KeyError as e:
            print('错误：' + file + '文件保存编码不为utf-8,现版本仅支持utf-8:' + str(e))
            logInfo('错误：' + file + '文件保存编码不为utf-8,现版本仅支持utf-8:' + str(e))
            # 跳过该文件，继续执行下一个文件
            continue
        chunkNum = 0

        for table in df:
            # 表头
            columnsList = table.columns.tolist()
            if file == '商标代理人信息.csv':
                chunkNum += chunkSize
                print('开始导入' + file + str(chunkNum) + ' 条数据到sql3中')
                sq3_tem_con = sqlite3.connect(db_name)
                sq3_tem_db = sq3_tem_con.cursor()
                # 数据
                print('读取数据')
                dataList = table.values.tolist()
                codeList = []
                nameDect = {}
                for data in dataList:
                    codeList.append("'" + str(data[0]) + "'")
                    nameDect[str(data[0])] = str(data[1])
                del dataList
                codeStr = ','.join(codeList)
                updateData = []
                sq3_tem_db.execute("select id,agencyCode from data where agencyCode in (" + codeStr + ")")
                del codeStr
                queryRes =sq3_tem_db.fetchall()
                if queryRes:
                    for res in queryRes:
                        if str(res[1]) in nameDect:
                            updateData.append([nameDect[str(res[1])], str(res[0])])
                del nameDect
                del queryRes
                sq3_tem_db.executemany('update data set agencyName = ? where id = ?', updateData)
                del updateData
                sq3_tem_con.commit()
                print('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')
                logInfo('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')

            elif file == '注册商标共有人信息.csv':
                chunkNum += chunkSize
                print('开始导入' + file + str(chunkNum) + ' 条数据到sql3中')
                sq3_tem_con = sqlite3.connect(db_name)
                sq3_tem_db = sq3_tem_con.cursor()
                # 数据
                print('读取数据')
                dataList = table.values.tolist()
                codeList = []
                dataJson = {}
                for data in dataList:
                    dataTem = {}
                    data = list(data)
                    codeList.append("'" + str(data[0]) + "'")
                    dataTem['brandCode'] = data[0]
                    dataTem['cnName'] = data[1]
                    dataTem['enName'] = data[2]
                    dataTem['cnAddress'] = data[3]
                    dataTem['enAddress'] = data[4]
                    dataJson[str(data[0])] = str(json.dumps(dataTem))
                del dataList
                codeStr = ','.join(codeList)
                del codeList
                updateData = []
                sq3_tem_db.execute("select id,brandCode from data where brandCode in (" + codeStr + ")")
                del codeStr
                queryRes = sq3_tem_db.fetchall()
                if queryRes:
                    for res in queryRes:
                        if str(res[1]) in dataJson:
                            updateData.append([dataJson[str(res[1])], str(res[0])])
                del dataJson
                del queryRes
                sq3_tem_db.executemany('update data set shareData = ? where id = ?', updateData)
                del updateData
                sq3_tem_con.commit()
                print('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')
                logInfo('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')

            elif file == '国际注册基本信息.csv':
                chunkNum += chunkSize
                print('开始导入' + file + str(chunkNum) + ' 条数据到sql3中')
                updateSql = []
                key = 0
                # 文件中含有dataIndex中字段的key位置
                dataKey = []
                for column in columnsList:
                    if column in dataIndex:
                        if not dataIndex[column][0] == 'brandCode':
                            updateSql .append(dataIndex[column][0] + '= ? ')
                        dataKey.append(key)
                    key += 1
                updateSql = ','.join(updateSql)
                sq3_tem_con = sqlite3.connect(db_name)
                sq3_tem_db = sq3_tem_con.cursor()
                # 数据
                print('读取数据')
                dataList = table.values.tolist()
                codeList = []
                updateDataDect = {}
                for data in dataList:
                    updateDataDect[str(data[0])] = []
                    for dk in dataKey:
                        if dk == 0:
                            continue
                        updateDataDect[str(data[0])].append(data[dk])
                    codeList.append("'" + str(data[0]) + "'")
                del dataList
                codeStr = ','.join(codeList)
                updateData = []
                sq3_tem_db.execute("select id,brandCode from data where brandCode in (" + codeStr + ")")
                del codeStr
                queryRes = sq3_tem_db.fetchall()
                if queryRes:
                    for res in queryRes:
                        if res[1] in updateDataDect:
                            updateData.append(updateDataDect[res[1]] + [str(res[0])])
                del updateDataDect
                del queryRes
                sq3_tem_db.executemany('update data set ' + updateSql + ' where id = ?', updateData)
                del updateData
                sq3_tem_con.commit()
                print('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')
                logInfo('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')

            elif file == '注册商标优先权信息.csv':
                chunkNum += chunkSize
                print('开始导入' + file + str(chunkNum) + ' 条数据到sql3中')
                sq3_tem_con = sqlite3.connect(db_name)
                sq3_tem_db = sq3_tem_con.cursor()
                # 数据
                print('读取数据')
                dataList = table.values.tolist()
                brandCodeList = []
                dataJson = {}
                for data in dataList:
                    dataTem = {}
                    data = list(data)
                    brandCodeList.append("'" + str(data[0]) + "'")
                    dataTem['priorityCode'] = data[2]
                    dataTem['priorityType'] = data[3]
                    dataTem['priorityDate'] = data[4]
                    dataTem['priorityGoods'] = data[5]
                    dataTem['priorityAddress'] = data[6]
                    dataJson[str(data[0]) + str(data[1])] = str(json.dumps(dataTem))
                del dataTem
                codeStr = ','.join(brandCodeList)
                updateData = []
                sq3_tem_db.execute("select id,brandCode,classNum from data where brandCode in (" + codeStr + ")")
                queryRes = sq3_tem_db.fetchall()
                if queryRes:
                    for res in queryRes:
                        if str(res[1]) + str(res[2]) in dataJson:
                            updateData.append([dataJson[str(res[1]) + str(res[2])], res[0]])
                del dataJson
                sq3_tem_db.executemany('update data set priorityData = ? where id = ?', updateData)
                del updateData
                sq3_tem_con.commit()
                print('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')
                logInfo('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')

            elif file == '注册商标商品服务信息.csv':
                chunkNum += chunkSize
                print('开始导入' + file + str(chunkNum) + ' 条数据到sql3中')
                sq3_tem_con = sqlite3.connect(db_name)
                sq3_tem_db = sq3_tem_con.cursor()
                # 数据
                print('读取数据')
                dataList = table.values.tolist()
                codeList = []
                dataJson = {}
                for data in dataList:

                    dataTem = {}
                    data = list(data)
                    codeList.append("'" + str(data[0]) + "'")
                    dataTem['brandCode'] = data[0]
                    dataTem['classNum'] = data[1]
                    dataTem['goodsGroup'] = data[2]
                    dataTem['goodsNum'] = data[3]
                    dataTem['goodsCnName'] = data[4]
                    dataTem['goodsStatus'] = data[5]
                    dataJson[str(data[0]) + str(data[1])] = str(json.dumps(dataTem))

                del dataList

                codeStr = ','.join(codeList)
                updateData = []
                sq3_tem_db.execute("select id,brandCode,classNum from data where brandCode in (" + codeStr + ")")
                queryRes = sq3_tem_db.fetchall()
                if queryRes:
                    for res in queryRes:
                        if str(res[1]) + str(res[2]) in dataJson:
                            updateData.append([dataJson[str(res[1]) + str(res[2])], res[0]])
                del dataJson
                sq3_tem_db.executemany('update data set goodsData = ? where id = ?', updateData)
                del updateData
                sq3_tem_con.commit()
                print('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')
                logInfo('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')

            elif file == '商标注册人信息.csv':
                chunkNum += chunkSize
                print('开始导入' + file + str(chunkNum) + ' 条数据到sql3中')
                updateSql = []
                key = 0
                # 文件中含有dataIndex中字段的key位置
                dataKey = []
                for column in columnsList:
                    if column in dataIndex:
                        if not dataIndex[column][0] == 'brandCode' and not dataIndex[column][0] == 'classNum':
                            updateSql .append(dataIndex[column][0] + '= ? ')
                        dataKey.append(key)
                    key += 1
                updateSql = ','.join(updateSql)
                sq3_tem_con = sqlite3.connect(db_name)
                sq3_tem_db = sq3_tem_con.cursor()
                # 数据
                print('读取数据')
                dataList = table.values.tolist()
                codeList = []
                updateDataDict = {}
                for data in dataList:
                    tem = []
                    for dk in dataKey:
                        if dk == 0 or dk == 1:
                            continue
                        tem.append(data[dk])
                    updateDataDict[str(data[0]) + str(data[1])] = tem
                    codeList.append("'" + str(data[0]) + "'")
                del dataList
                codeStr = ','.join(codeList)
                updateData = []
                sq3_tem_db.execute("select id,brandCode,classNum from data where brandCode in (" + codeStr + ")")
                del codeStr
                queryRes = sq3_tem_db.fetchall()
                if queryRes:
                    for res in queryRes:
                        if str(str(res[1]) + str(res[2])) in updateDataDict:
                            updateData.append(updateDataDict[str(res[1]) + str(res[2])] + [str(res[0])])
                del updateDataDict
                del queryRes
                sq3_tem_db.executemany('update data set ' + updateSql + ' where id = ?', updateData)
                del updateData
                sq3_tem_con.commit()
                print('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')
                logInfo('批量更新 ' + file + str(chunkNum) + ' 条数据到sql3中，完成')
            else:
                break
    print('导入所有数据到sqlite3完成，开始导入mysql')
    logInfo('导入所有数据到sqlite3完成，开始导入mysql')
    del df
    toMysql()
    print('导入操作完成，结束进程')
    logInfo('导入操作完成，结束进程')
    exit()


# 合并两字典
def Merge(dict1, dict2):
    return {**dict1, **dict2}

# todo 待完成
def toMysql():
    mysqlCon = pymysql.connect(configMysql['host'], configMysql['user'], configMysql['pass'], configMysql['database'])
    mysqlDb = mysqlCon.cursor()

    sq3QueryCon = sqlite3.connect(db_name)
    sq3QueryDb = sq3QueryCon.cursor()
    # 获取sql3中有多少条数据
    sq3QueryDb.execute('select count() from data')
    num = 0
    total = int(sq3QueryDb.fetchone()[0])
    # 获取sql3中所有的字段名字
    sq3QueryDb.execute('PRAGMA table_info([data])')
    sql3ColumnsListTem = sq3QueryDb.fetchall()
    sql3ColumnsList = []
    for sql3Column in sql3ColumnsListTem:
        sql3ColumnsList.append(sql3Column[1])

    # 获取 mysql 中所有的字段名字

    mysqlDb.execute('select * from ' + configMysql['table'] + ' limit 1')
    mysqlColumnsListTem = mysqlDb.description
    mysqlColumnsList = []
    # 组装sql和取对应关系
    insertSql = ''
    # sql3中字段名在mysql中存在的位置列表
    columnData = []
    insertListTem = []
    for mysqlColumn in mysqlColumnsListTem:
        if not mysqlColumn[0] == 'id':
            insertListTem.append(0)
            insertSql += str(mysqlColumn[0]) + '=? '
            if mysqlColumn[0] in sql3ColumnsList:
                columnData.append(sql3ColumnsList.index(mysqlColumn[0]))
        mysqlColumnsList.append(mysqlColumn[0])

    # 清空原有数据
    # mysqlDb.execute('truncate table ' + configMysql['table'])
    # mysqlDb.commit()
    print(columnData)
    pageTotal = (total - total % chunkSize) / chunkSize
    while num <= pageTotal + 1:
        # 分页查询sql3中数据
        sq3QueryDb.execute('select * from data limit ' + str(num * chunkSize) + ',' + str(chunkSize))
        sql3Res = sq3QueryDb.fetchall()
        insertList = []
        for sql3ResData in sql3Res:
            # 导入MySQL中的数据
            for count in range(0, len(sql3ResData)):
                if count in columnData:
                    insertListTem[count] = sql3ResData[count]
            insertList.append(insertListTem)
            print(insertList)
            exit()
        num += 1
    print(sq3QueryDb.fetchone())

    mysqlDb.execute('select * from tm_trademarks where id = 38454403')
    print(mysqlDb.fetchone())


if __name__ == '__main__':
    import_data()
